package fy.hr.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.json.JSONObject;

import fy.config.FyConfig;
import fy.util.DBUtil;


/**
 * 应聘候选人评估推荐表
 * 
 * @author Administrator
 * 
 */
public class RecommendFormDao {
	private static Log log = LogFactory.getLog(RecommendFormDao.class);

	/**
	 * 根据单据编号查询人员需求申请单信息
	 * @param number
	 * @return
	 */
	public static String queryRequirementInfo(String number) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		boolean success = false;
		JSONObject jo = null;
		String msg = null;
		try {
			conn = fy.util.DBUtil.connect(FyConfig.getOA_DB_CONFIG());

			pst = conn
					.prepareStatement("select 申请编号, 申请公司,申请部门 , 申请人, 申请原因, 申请岗位, 需求人数, 职级, 申请日期, 需求签批日期,要求到位日期, 性别,年龄,学历,专业,工作经验,招聘负责人 from fy_requirement_view where 申请编号 = ?");

			pst.setString(1, number);

			rs = pst.executeQuery();
			// select 申请编号, 申请公司,申请部门 , 申请人, 申请原因, 申请岗位, 需求人数, 职级, 申请日期,
			// 需求签批日期,要求到位日期, 性别,年龄,学历,专业,工作经验 from fy_requirement_view where
			// 申请编号 = ?
			jo = new JSONObject();
			JSONObject data = new JSONObject();
			if (rs.next()) {
				jo.put("success", true);

				ResultSetMetaData md = rs.getMetaData();

				for (int i = 1, size = md.getColumnCount(); i <= size; i++) {
					String name = md.getColumnName(i);
					data.put(name, rs.getString(name));
				}
				jo.put("data", data);
			} else {
				jo.put("success", false);
				jo.put("data", "未查到该单据编号对应的《人员需求申请表》");
			}

			success = true;

		} catch (Exception e) {
			msg = "数据库异常，请重试，或者联系OA系统管理员。";
			log.error("数据库异常", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return success ? jo.toString() : new StringBuilder()
				.append("{success : false, data : '").append(msg).append("'}")
				.toString();
	}
/**
 * 根据人员身份证号从HR系统人才库中读取人员信息
 * @param number
 * @return
 */
	public static String queryRequirementPersonInfo(String number) {
		Connection conn = null;
		PreparedStatement pst = null;
		ResultSet rs = null;
		boolean success = false;
		JSONObject jo = null;
		String msg = null;
		try {
			conn = DBUtil.connect(FyConfig.getEHR_DB_CONFIG());

			pst = conn
					.prepareStatement("select id as 人员身份证号, psnname as 姓名,sex as 性别,ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), to_date(birthdate,'yyyy-mm-dd')) * 1.0 / 12, 1) as 年龄,nvl(d1.docname,'无数据') as 学历,nvl(d2.docname,'无数据') as 专业,'无数据' as 工龄,d3.docname as 招聘渠道,nvl(reg_expect_wage,0) as 期望薪资 from rm_psnbasdoc tab1 left join rm_psndoc_edu tab2 on tab1.pk_psnbasdoc = tab2.pk_psnbasdoc and tab2.lasteducation='Y' left join bd_defdoc d1 on d1.pk_defdoc  = education left join bd_defdoc d2 on d2.pk_defdoc  = major left join bd_defdoc d3 on d3.pk_defdoc  = reg_pk_channel where id=?");

			pst.setString(1, number);

			rs = pst.executeQuery();
			
			jo = new JSONObject();
			JSONObject data = new JSONObject();
			if (rs.next()) {
				jo.put("success", true);

				ResultSetMetaData md = rs.getMetaData();

				for (int i = 1, size = md.getColumnCount(); i <= size; i++) {
					String name = md.getColumnName(i);
					data.put(name, rs.getString(name));
				}
				jo.put("data", data);
			} else {
				jo.put("success", false);
				jo.put("data", "未查到该身份证号对应的人员信息");
			}

			success = true;

		} catch (Exception e) {
			msg = "数据库异常，请重试，或者联系OA系统管理员。";
			log.error("数据库异常", e);
			e.printStackTrace();
		} finally {
			DBUtil.close(rs);
			DBUtil.close(pst);
			DBUtil.close(conn);
		}
		return success ? jo.toString() : new StringBuilder()
				.append("{success : false, data : '").append(msg).append("'}")
				.toString();
	}
}
